SQL Table Editor Filtering

The SQL Table Editor lets you filter results using the following two methods, or modes:

  • Popup Mode: This is the default filtering mode and has been available for use in all prior releases of the OneStream software application. Using this mode, you can activate filtering by clicking the funnel icon in the column header, which launches a popup window.

    The popup contains two field filters that let you create filtering criteria such as "value is greater than 18 and is less than 60." Field filters consist of two parts: an upper part, which is a combo box, and a lower part called the field filter editor. In the upper part (combo box), you can use filter operators like Is Equal To or Is Less Than, which change based on the data type of the column. The lower part (field filter editor) provides filtering criteria based on the data type of the column. For example, if you have a date column, you can use a date/time picker control as the filtering criteria.

  • FilterRow Mode: This filtering mode is built into the header cell of each filterable column. It is simpler to use than Popup mode and uses only one field editor to filter. All filters start as empty until a value is entered into the editor. Click the filter icon to display a drop-down list of all operators available to the data type of the column. If you select Clear Filter from the list, it will clear and reset the column filter.

You can configure the Filter Mode property of the SQL Table Editor to use either of the modes. To configure the SQL Table Editor:

  1. Navigate to Application tab > Workspaces > Maintenance Units > Components > SQL Table Editor.

  2. In the SQL Table Editor section settings, set Filter Mode to either Popup (default) or Filter Row.

NOTE: Consolidated column filtering is not supported on the following data types. Applying filters on these datatypes will not yield consolidated filtered results. Unsupported data types: binary, char, date, datetime2, datetimeoffset, float, geography, geometry, hierarchyid, image, filestream, money, nchar, ntext, numeric, nvarchar, real, rowversion, smalldatetime, smallmoney, sql_variant, text, time, timestamp, tinyint, varbinary, xml.

Filtering Columns Based on Values from Another Column

You can base a column’s header filter on values from another column. This includes any column that is in the data set, even if the column is hidden. This is useful for name-value pair parameter columns as long as the values and names are pulled from individual columns.

To set up and configure the SQL Table Editor component:

  1. Inside the appropriate workspace, create and configure an SQL Table Editor component according to your needs.

  2. Identify the column that you want to filter along with the column the filter is based upon.

  3. In the SQL Table Editor component, configure the column format for the column you want to filter by typing the name of the column into the FilterByColumnName property.

Example

Begin with an SQL Table Editor grid. The data set can come from an SQL view or an SQL table.

 

When filtering data using a column header filter, the default behavior is to filter by the value stored in that column. For example, filtering the ProdID column on a value of 10 returns one row where ProdID equals 10.

If you would like to change the ProdID column filter and base it on ProductName instead, configure the column format for ProdID by setting FilterByColumnName to ProductName.

Column filtering for column ProdID is now based on values from the ProductName column.